package com.xm.dao.background;

import com.xm.entity.*;
import com.xm.entity.Class;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import com.xm.util.DiscountConverterUtil;
import org.intellij.lang.annotations.Language;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 吴添文
 * @date 2024年1月3日
 */
public class CommodityDao extends BaseUtil {

    /**
     * 分页查询
     *
     * @param pageNumber 分页页数
     * @param pageSize   每页数量
     * @return page<对象>
     */
    public Page<Commodity> pagination(int pageNumber, int pageSize, String name, Integer state) {
        Page<Commodity> page = new Page<>();
        List<Commodity> commodityList = new ArrayList<>();
        @Language("sql")
        String countSql = "SELECT COUNT(*) FROM commodity where 1 = 1";
        @Language("sql")
        String dataSql = "SELECT * FROM commodity where 1 = 1 ";
        ResultSet rs = null;
        if (name != null) {
            if (isNumeric(name)) {
                countSql += " and commodity_id like '%" + name + "%'";
                dataSql += " and commodity_id like '%" + name + "%'";
            } else {
                countSql += " and commodity_name like '%" + name + "%'";
                dataSql += " and commodity_name like '%" + name + "%'";
            }
        }
        int offShelves = 2;
        if (state != null && state == 0) {
            dataSql += " ORDER BY shelf_time,commodity_state ASC ";
        } else if (state != null && offShelves == state) {
            dataSql += " ORDER BY shelf_time,commodity_state DESC ";
        } else {
            dataSql += " ORDER BY shelf_time DESC ";
        }
        dataSql += " LIMIT ?, ?";

        try {
            // 查询总数
            rs = executeQuery(countSql);
            if (rs.next()) {
                int count = rs.getInt(1);
                page.setCount(count);
                page.setTotalPages(pageSize);
            }

            // 分页查询数据
            int offset = (pageNumber - 1) * pageSize;
            rs = executeQuery(dataSql, offset, pageSize);
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityState(rs.getInt("commodity_state"));
                commodity.setShelfTime(rs.getDate("shelf_time"));
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodityList.add(commodity);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
            close(null);
        }
        page.setData(commodityList);
        page.setPageNumber(pageNumber);
        page.setPageSize(pageSize);
        return page;
    }

    /**
     * 修改状态
     *
     * @param commodityId 商品ID
     * @param status      状态,0是上架，2是下架
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateAllSelectedStatus(Integer commodityId, Integer status) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_state = ? WHERE commodity_id = ?;";
        Object[] params = {status, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * @param str 参数
     * @return true:数字/false:字符串
     */
    public static boolean isNumeric(String str) {
        return str != null && str.matches("-?\\d+(\\.\\d+)?");
    }

    /**
     * 根据商品Id查询
     *
     * @param commodityId 商品Id
     * @return 对象
     */
    public Commodity getCommodityById(Integer commodityId) {
        ResultSet rs = null;
        ResultSet rsSpecification = null;
        ResultSet rsSpecificationType = null;
        ResultSet rsInsuranceType = null;
        ResultSet rsInsurance = null;
        ResultSet rsParameter = null;
        //查询商品、赠品、类别
        @Language("sql")
        String sql = "SELECT c.*,cg.gifts_number,commodity_g.commodity_id,commodity_g.commodity_name, " +
                "cl.class_id,cl.class_name,ca.category_id,ca.category_name,m.model_id,m.model_name, " +
                "cd.details_id,cd.details_images,cd.details_video,cs.* " +
                "FROM commodity c " +
                "left join commodity_gifts cg on cg.commodity_gifts_id = c.commodity_gifts " +
                "left join commodity commodity_g on cg.commodity_id = commodity_g.commodity_id " +
                "left join class cl on c.commodity_class = cl.class_id " +
                "left join category ca on cl.category_id = ca.category_id " +
                "left join model m on ca.model_id = m.model_id " +
                "left join commodity_details cd on c.commodity_id = cd.commodity_id " +
                "left join commodity_serve cs on c.commodity_id = cs.commodity_id " +
                "WHERE c.commodity_id = ? ";
        //查询商品规格类别
        @Language("SQL")
        String specificationTypeSql = "select DISTINCT cst.specification_type_id,cst.specification_type_id,cst.specification_type_name  from commodity_specification cs " +
                "left join commodity_specification_type cst on cs.specification_type_id = cst.specification_type_id " +
                "where commodity_id = ?";
        //查询商品规格
        @Language("SQL")
        String specificationSql = "select cs.specification_id,cs.specification_name,cs.add_price,cs.specification_id " +
                "from commodity_specification cs " +
                "where cs.commodity_id = ? and cs.specification_type_id = ?";
        //商品保险
        @Language("SQL")
        String insuranceTypeSql = "select DISTINCT cit.insurance_type_id,cit.insurance_type_logo,cit.insurance_type_title" +
                " from commodity_insurance_type cit " +
                "left join commodity_insurance ci on cit.insurance_type_id = ci.insurance_type_id " +
                "where ci.commodity_id = ?";
        @Language("SQL")
        String insuranceSql = "select ci.insurance_title,ci.insurance_id,ci.insurance_preferential" +
                ",ci.insurance_context,ci.commodity_id,ci.insurance_issue,ci.add_price from commodity_insurance ci " +
                "where ci.commodity_id = ? and ci.insurance_type_id = ?";
        @Language("SQL")
        String parameterSql = "select * from commodity_parameter where commodity_id = ?";
        Commodity commodity = new Commodity();
        try {
            rs = executeQuery(sql, commodityId);
            if (rs.next()) {
                commodity.setCommodityId(rs.getInt("c.commodity_id"));
                commodity.setCommodityName(rs.getString("c.commodity_name"));
                commodity.setCommodityDiscount(rs.getInt("c.commodity_discount"));
                commodity.setCommodityImages(rs.getString("c.commodity_images"));
                commodity.setKeywords(rs.getString("c.keywords"));
                commodity.setCommodityState(rs.getInt("c.commodity_state"));
                commodity.setCommodityDescription(rs.getString("c.commodity_description"));
                commodity.setCommodityGifts(rs.getInt("c.commodity_gifts"));
                commodity.setShelfTime(rs.getDate("c.shelf_time"));
                commodity.setCommodityPrice(rs.getBigDecimal("c.commodity_price"));
                commodity.setCommodityClass(rs.getInt("c.commodity_class"));
                commodity.setCommodityServe(new CommodityServe(rs.getInt("serve_id"),
                        rs.getInt("is_self_employed"),rs.getInt("is_mi_shipments"),
                        rs.getInt("is_return"),rs.getInt("is_price_protection"),
                        rs.getInt("is_shipping_instructions"),rs.getInt("is_company_information"),
                        rs.getInt("is_after_sales")));
                commodity.setType(new Class(rs.getString("class_name"),
                        new Category(rs.getString("category_name"),new Model(rs.getString("model_name")))));
                //商品详情
                commodity.setCommodityDetails(
                        new CommodityDetails(
                                rs.getInt("cd.details_id"), rs.getString("cd.details_images"),
                                rs.getString("cd.details_video")));

                // 使用getGiftByGiftId方法获取赠品信息
                Integer giftId = rs.getInt("commodity_gifts");
                CommodityGifts gift = getGiftByGiftId(giftId);
                commodity.setGifts(gift);
            }
            //根据商品Id查询商品参数
            rsParameter = executeQuery(parameterSql,commodityId);
            List<CommodityParameter> commodityParameterList = new ArrayList<>();
            while (rsParameter.next()){
                CommodityParameter commodityParameter = new CommodityParameter();
                commodityParameter.setParameterId(rsParameter.getInt("parameter_id"));
                commodityParameter.setParameterImages(rsParameter.getString("parameter_images"));
                commodityParameter.setParameterTitle(rsParameter.getString("parameter_title"));
                commodityParameter.setParameterContext(rsParameter.getString("parameter_context"));
                commodityParameterList.add(commodityParameter);
            }
            commodity.setCommodityParameterList(commodityParameterList);

            //根据商品规格查询商品规格类别
            rsSpecificationType = executeQuery(specificationTypeSql, commodityId);
            List<CommoditySpecificationType> commoditySpecificationTypeList = new ArrayList<>();
            while (rsSpecificationType.next()) {
                CommoditySpecificationType commoditySpecificationType = new CommoditySpecificationType();
                List<CommoditySpecification> commoditySpecificationList = new ArrayList<>();
                //根据商品Id和商品规格类别Id查询商品规格
                rsSpecification = executeQuery(specificationSql, commodityId,
                        rsSpecificationType.getInt("specification_type_id"));
                while (rsSpecification.next()) {
                    CommoditySpecification commoditySpecification = new CommoditySpecification();
                    commoditySpecification.setSpecificationId(rsSpecification.getInt("specification_id"));
                    commoditySpecification.setSpecificationName(rsSpecification.getString("specification_name"));
                    commoditySpecification.setAddPrice(rsSpecification.getBigDecimal("add_price"));
                    commoditySpecificationList.add(commoditySpecification);
                }
                commoditySpecificationType.setCommoditySpecification(commoditySpecificationList);
                commoditySpecificationType.setSpecificationTypeId(
                        rsSpecificationType.getInt("specification_type_id"));
                commoditySpecificationType.setSpecificationTypeName(
                        rsSpecificationType.getString("specification_type_name"));
                commoditySpecificationTypeList.add(commoditySpecificationType);
            }


            commodity.setCommoditySpecificationTypeList(commoditySpecificationTypeList);
            //商品保险
            rsInsuranceType = executeQuery(insuranceTypeSql, commodityId);

            List<CommodityInsuranceType> commodityInsuranceTypeList = new ArrayList<>();
            //保险类别
            while (rsInsuranceType.next()) {
                CommodityInsuranceType commodityInsuranceType = new CommodityInsuranceType();
                commodityInsuranceType.setInsuranceTypeId(rsInsuranceType.
                        getInt("insurance_type_id"));
                commodityInsuranceType.setInsuranceTypeLogo(rsInsuranceType.getString("insurance_type_logo"));
                commodityInsuranceType.setInsuranceTypeTitle(rsInsuranceType.
                        getString("insurance_type_title"));

                rsInsurance = executeQuery(insuranceSql, commodityId, rsInsuranceType.getInt("insurance_type_id"));
                List<CommodityInsurance> commodityInsuranceList = new ArrayList<>();
                //保险
                while (rsInsurance.next()) {
                    CommodityInsurance commodityInsurance = new CommodityInsurance();
                    commodityInsurance.setInsuranceId(rsInsurance.getInt("insurance_id"));
                    commodityInsurance.setInsuranceTitle(rsInsurance.getString("insurance_title"));
                    commodityInsurance.setInsuranceContext(rsInsurance.getString("insurance_context"));
                    commodityInsurance.setInsurancePreferential(new BigDecimal(
                            rsInsurance.getString("insurance_preferential") == null
                                    ? "0.00" : rsInsurance.getString("insurance_preferential")));
                    commodityInsurance.setInsuranceIssue(rsInsurance.getString("insurance_issue"));
                    commodityInsurance.setAddPrice(new BigDecimal(
                            rsInsurance.getString("add_price") == null ?
                                    "0.00" : rsInsurance.getString("add_price")));
                    commodityInsuranceList.add(commodityInsurance);
                }
                commodityInsuranceType.setCommodityInsurances(commodityInsuranceList);
                commodityInsuranceTypeList.add(commodityInsuranceType);
            }
            commodity.setCommodityInsuranceTypeList(commodityInsuranceTypeList);
        } catch (SQLException e) {
            throw new RuntimeException("SQL语句错误", e);
        } finally {
            close(rs);
            close(rsSpecificationType);
            close(rsSpecification);
            close(rsInsuranceType);
            close(rsInsurance);
            close(rsParameter);
        }
        return commodity;
    }

    /**
     * 根据商品Id查询
     *
     * @param commodityId 商品Id
     * @return 对象
     */
    public Commodity getCommodityByIdAndByWarehouseId(Integer commodityId, Integer warehouseId) {
        ResultSet rs = null;
        @Language("sql")
        String sql = "SELECT c.*, w.details_inventory, w.purchase_limit " +
                "FROM commodity c " +
                "LEFT JOIN commodity_warehouse w ON c.commodity_id = w.commodity_id " +
                "WHERE c.commodity_id = ? AND w.warehouse_id = ?";
        Commodity commodity = new Commodity();
        try {
            rs = executeQuery(sql, commodityId, warehouseId);
            if (rs.next()) {
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityDiscount(rs.getInt("commodity_discount"));
                commodity.setCommodityImages(rs.getString("commodity_images"));
                commodity.setKeywords(rs.getString("keywords"));
                commodity.setCommodityState(rs.getInt("commodity_state"));
                commodity.setCommodityDescription(rs.getString("commodity_description"));
                commodity.setCommodityGifts(rs.getInt("commodity_gifts"));
                commodity.setShelfTime(rs.getDate("shelf_time"));
                commodity.setCommodityPrice(rs.getInt("commodity_discount") == 0 ? rs.getBigDecimal("commodity_price") :
                        DiscountConverterUtil.calculateDiscount(rs.getBigDecimal("commodity_price"),rs.getInt("commodity_discount")));
                commodity.setCommodityClass(rs.getInt("commodity_class"));

                CommodityWarehouse warehouse = new CommodityWarehouse();
                warehouse.setDetailsInventory(rs.getInt("details_inventory"));
                warehouse.setPurchaseLimit(rs.getInt("purchase_limit"));
                commodity.setCommodityWarehouse(warehouse);

                // 使用getGiftByGiftId方法获取赠品信息
                Integer giftId = rs.getInt("commodity_gifts");
                CommodityGifts gift = getGiftByGiftId(giftId);
                commodity.setGifts(gift);
            }
        } catch (SQLException e) {
            throw new RuntimeException("SQL语句错误", e);
        } finally {
            close(rs);
            close(null);
        }
        return commodity;
    }

    /**
     * 根据多个ID查询商品及其赠品和仓库信息
     *
     * @param commodityIds 多个商品ID
     * @param warehouseId  仓库ID
     * @return 商品列表
     * @throws SqlErrorException SQL执行异常
     */
    public List<Commodity> getCommoditiesWithGiftsAndWarehouseByIds(String[] commodityIds, Integer warehouseId) throws SqlErrorException {
        String inCondition = String.join(",", Collections.nCopies(commodityIds.length, "?"));
        @Language("sql")
        String sql = "SELECT c.*, w.details_inventory, w.purchase_limit " +
                "FROM commodity c " +
                "LEFT JOIN commodity_warehouse w ON c.commodity_id = w.commodity_id " +
                "WHERE c.commodity_id IN (" + inCondition + ") AND w.warehouse_id = ?";
        ResultSet resultSet = null;
        try {
            List<Object> params = new ArrayList<>(Arrays.asList(commodityIds));
            params.add(warehouseId);
            resultSet = executeQuery(sql, params.toArray());
            Map<Integer, Commodity> commodityMap = new HashMap<>(10000);
            while (resultSet.next()) {
                int commodityId = resultSet.getInt("commodity_id");
                Commodity commodity = commodityMap.get(commodityId);
                if (commodity == null) {
                    commodity = new Commodity();
                    commodity.setCommodityId(commodityId);
                    commodity.setCommodityImages(resultSet.getString("commodity_images").split(",")[0]);
                    commodity.setCommodityName(resultSet.getString("commodity_name"));
                    commodity.setCommodityState(resultSet.getInt("commodity_state"));
                    commodity.setShelfTime(resultSet.getDate("shelf_time"));
                    commodity.setCommodityPrice(resultSet.getInt("commodity_discount") == 0 ? resultSet.getBigDecimal("commodity_price") :
                            DiscountConverterUtil.calculateDiscount(resultSet.getBigDecimal("commodity_price"),resultSet.getInt("commodity_discount")));
                    commodityMap.put(commodityId, commodity);
                }
                int giftId = resultSet.getInt("commodity_gifts");
                if (giftId != 0) {
                    CommodityGifts gift = getGiftByGiftId(giftId);
                    commodity.setGifts(gift);
                }

                CommodityWarehouse warehouse = new CommodityWarehouse();
                warehouse.setDetailsInventory(resultSet.getInt("details_inventory"));
                warehouse.setPurchaseLimit(resultSet.getInt("purchase_limit"));
                commodity.setCommodityWarehouse(warehouse);
            }
            return new ArrayList<>(commodityMap.values());
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 根据赠品ID查询赠品信息
     *
     * @param giftId 赠品ID
     * @return 赠品对象
     */
    private CommodityGifts getGiftByGiftId(int giftId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT cg.*, c.* FROM commodity_gifts cg INNER JOIN commodity c " +
                "ON cg.commodity_id = c.commodity_id WHERE cg.commodity_gifts_id = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, giftId);
            if (resultSet.next()) {
                CommodityGifts gift = new CommodityGifts();
                gift.setCommodityGiftsId(resultSet.getInt("commodity_gifts_id"));
                gift.setCommodityId(resultSet.getInt("commodity_id"));
                gift.setGiftsNumber(resultSet.getInt("gifts_number"));
                gift.setGiftsBeginDate(resultSet.getTimestamp("gifts_begin_date"));
                gift.setGiftsEndDate(resultSet.getTimestamp("gifts_end_date"));

                Commodity commodity = new Commodity();
                commodity.setCommodityId(resultSet.getInt("c.commodity_id"));
                commodity.setCommodityImages(resultSet.getString("commodity_images").split(",")[0]);
                commodity.setCommodityName(resultSet.getString("commodity_name"));
                commodity.setCommodityState(resultSet.getInt("commodity_state"));
                commodity.setShelfTime(resultSet.getDate("shelf_time"));
                commodity.setCommodityPrice(resultSet.getBigDecimal("commodity_price"));

                gift.setCommodity(commodity);

                return gift;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询赠品失败", e);
        } finally {
            close(resultSet);
        }
        return null;
    }

    /**
     * 根据商品Id修改商品名称
     *
     * @param commodityId        商品Id
     * @param commodityImageName 商品名称
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityImgNameById(Integer commodityId, String commodityImageName) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_images = ? WHERE commodity_id = ?";
        Object[] params = {commodityImageName, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品Id修改商品名称和keywords
     *
     * @param commodityId 商品Id
     * @param keywords    商品关键词
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updatedKeywordsById(Integer commodityId, String keywords) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET keywords = ? WHERE commodity_id = ?";
        Object[] params = {keywords, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品Id修改商品描述
     *
     * @param commodityId          商品Id
     * @param commodityDescription 商品描述
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityDescriptionById(Integer commodityId, String commodityDescription) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_description = ? WHERE commodity_id = ?";
        Object[] params = {commodityDescription, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品ID修改商品赠品
     *
     * @param commodityId    商品ID
     * @param commodityGifts 新的商品赠品
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityGiftsById(Integer commodityId, Integer commodityGifts) throws SqlErrorException {
        @Language("SQL")
        String sql = "UPDATE commodity SET commodity_gifts = ? WHERE commodity_id = ?";
        Object[] params = {commodityGifts, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品ID修改商品折扣
     *
     * @param commodityId       商品ID
     * @param commodityDiscount 新的商品折扣
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityDiscountById(Integer commodityId, Integer commodityDiscount) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_discount = ? WHERE commodity_id = ?";
        Object[] params = {commodityDiscount, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品ID修改商品价格
     *
     * @param commodityId 商品ID
     * @param newPrice    新的商品价格
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityPriceById(Integer commodityId, BigDecimal newPrice) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_price = ? WHERE commodity_id = ?";
        Object[] params = {newPrice, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 根据商品ID修改商品名称
     *
     * @param commodityId 商品ID
     * @param newName     新的商品名称
     * @return 修改成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCommodityNameById(Integer commodityId, String newName) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE commodity SET commodity_name = ? WHERE commodity_id = ?";
        Object[] params = {newName, commodityId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }
    /**
     * 新增商品
     *
     * @param commodity 商品对象
     * @return 新增成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public Integer addCommodity(Commodity commodity) throws SqlErrorException {
        @Language("sql")
        String sql = "INSERT INTO commodity (commodity_name, commodity_images, keywords, commodity_state, " +
                "commodity_description, commodity_price, commodity_class) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {commodity.getCommodityName(), commodity.getCommodityImages(), commodity.getKeywords(),
                2, commodity.getCommodityDescription(), commodity.getCommodityPrice(),
                commodity.getCommodityClass()};
        return executeUpdate(sql, params);
    }
}
